/*
 * Copyright 1995-2010 Elcom Corp.  All rights reserved.
 */
package com.elcom.emocbc.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.jdbc.pool.OracleDataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.elcom.emocbc.util.ConfigurationManager;

/**
 * Created On: Jun 9, 2010 Created By: nguyen thai binh
 * <mailto:binhnt_esoft@elcom.com.vn>
 */
public class DatabaseConnection {

    private final Log LOG = LogFactory.getLog(DatabaseConnection.class);
    private OracleDataSource oracleDataSource = null;
    private static DatabaseConnection instance = null;

    private DatabaseConnection() {
        setupOracleDataSource();
    }

    public synchronized static DatabaseConnection newInstance() {
        if (instance == null) {
            instance = new DatabaseConnection();
        }
        return instance;
    }

    public synchronized DatabaseConnection reConnectionDB() {
        setupOracleDataSource();
        LOG.info("Finished reconnectDB......");
        return instance;
    }

    public synchronized Connection getConn() throws SQLException {
        if (oracleDataSource == null) {
            setupOracleDataSource();
            return oracleDataSource.getConnection();
        } else {
            return oracleDataSource.getConnection();
        }
    }

    public void closeConn(Connection conn) {
        try {
            if ((conn != null) && !conn.isClosed()) {
                conn.close();
            }
        } catch (Exception e) {
            LOG.error("Error when closeConn: ", e);
        }
    }

    public void closePreparedStatement(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
                LOG.error("Error when closePreparedStatement: ", e);
            }
        }
    }

    public void closeCallableStatement(CallableStatement cs) {
        if (cs != null) {
            try {
                cs.close();
            } catch (Exception e) {
                LOG.error("Error when closeCallableStatement: ", e);
            }
        }
    }

    public void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOG.error("Error when closeResultSet: ", e);
            }
        }
    }

    public void commitTransaction(Connection connection) {
        if (connection != null) {
            try {
                connection.commit();
            } catch (SQLException e) {
                LOG.error("Error when commit transaction: ", e);
            }
        }
    }

    public void rollbackTransaction(Connection connection) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                LOG.error("Error when rollbackTransaction: ", e);
            }
        }
    }

    public void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                LOG.error("Error when closeStatement: ", e);
            }
        }
    }

    private void setupOracleDataSource() {
        try {
            try {
                if (oracleDataSource != null) {
                    oracleDataSource.close();
                }
            } catch (Exception e) {
                LOG.error("Error when close OracleDataSource: ", e);
            }
            oracleDataSource = null;
            String connectURI = ConfigurationManager.getStrProp("cell_broadcast", "URL", "jdbc:oracle:thin:@192.168.2.125:1521:ORCL10G");
            String user = ConfigurationManager.getStrProp("cell_broadcast", "USER_NAME", "lbgw");
            String password = ConfigurationManager.getStrProp("cell_broadcast", "PASSWORD", "lbgw");
            String maxLimit = ConfigurationManager.getStrProp("cell_broadcast", "MAX_ACTIVE", "15");
            String maxTimeWait = ConfigurationManager.getStrProp("cell_broadcast", "MAX_WAIT", "35");
            oracleDataSource = new OracleDataSource();
            oracleDataSource.setURL(connectURI);
            oracleDataSource.setUser(user);
            oracleDataSource.setPassword(password);
            oracleDataSource.setImplicitCachingEnabled(true);
            oracleDataSource.setConnectionCachingEnabled(true);
            oracleDataSource.setFastConnectionFailoverEnabled(false);
            Properties cachedProps = new Properties();
            cachedProps.setProperty("MinLimit", "5"); // the cache size is
            // 5 at least
            cachedProps.setProperty("MaxLimit", maxLimit);
            cachedProps.setProperty("InitialLimit", "3"); // create 3
            // connections
            // at
            // startup
            cachedProps.setProperty("InactivityTimeout", "600"); // seconds
            cachedProps.setProperty("ConnectionWaitTimeout", maxTimeWait); // seconds
            cachedProps.setProperty("AbandonedConnectionTimeout", "300");
            cachedProps.setProperty("PropertyCheckInterval", "60"); // seconds
            cachedProps.setProperty("MaxStatementsLimit", "10");
            oracleDataSource.setConnectionCacheProperties(cachedProps);
        } catch (Exception e) {
            LOG.error("Error when setupOracleDataSource: ", e);
        }
    }
}
